import os
import re
import json
import requests
from bs4 import BeautifulSoup
import MySQLdb

url = 'http://fundf10.eastmoney.com/FundArchivesDatas.aspx?type=jjcc&code=161725&topline=10&year=2020&month=&rt=0.6224875128717713'

header = {
    'User-Agent' : 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/87.0.4280.88 Safari/537.36',
    'Referer' : 'http://fundf10.eastmoney.com/ccmx_161725.html'
}

resp = requests.get(url, headers=header)
### print(resp.text[12:])
str1 = resp.text[23:]
str2 = str1[:-61]
# print(str2)
# data2 = json.loads(str2)
###print(data2['content'])

Soup = BeautifulSoup(str2, 'html.parser')
tables = Soup.find('table')
trs = tables.select('tr')

# 打开数据库连接
db = MySQLdb.connect("localhost", "jijin", "jijin", "jijin", charset='utf8' )

cursor = db.cursor()

i = 0
for tr in trs:
    # ths = tr.select('th')
    # for th in ths:
    #     print(th.text, end="  ")
    if i == 0:
        i += 1
        continue
    else:
        tds = tr.select('td')
        # for td in tds:
        #     print(td.text, end="  ")
        # print('\n')

        sql = "INSERT INTO fundHoldings (fundId, years, stockId, stockName, \
            percentage, numbers, marketValue) VALUES (%s, %s, %s, %s, %s, %s, %s)"
        # print(tds)
    #   161725, 2020-4, tds[1].text, tds[2].text, tds[2].text, tds[4].text, tds[5].text, tds[6].text

        par = ("161725", "2020-3", tds[1].text, tds[2].text, tds[4].text, tds[5].text, tds[6].text)
        # print(par)
        try:
            cursor.execute(sql, par)
            db.commit()
        except:
            db.rollback()

db.close()

print("程序运行结束")
